<%@ page language="java" contentType="text/html; charset=ISO-8859-1" pageEncoding="ISO-8859-1" %>
<%@ page import="javax.mail.*" %>
<%@ page import="javax.mail.internet.*" %>
<%@ page import="java.util.*" %>
<%@ page import="java.sql.*" %>
<%@ page import="java.lang.*" %>
<%@ page import="java.io.*" %>
<%
try {
String connectionURL = "jdbc:mysql://webproject2.cse.ust.hk/BookwarmMobile?user=BookwarmMobile&password=bookwarm"; 

Connection connection = null; 

Class.forName("com.mysql.jdbc.Driver").newInstance(); 

connection = DriverManager.getConnection(connectionURL);

if(!connection.isClosed()) {
String newCopyID = null;
String newAccountName = null;

newCopyID = request.getParameter("copyid");
newAccountName = request.getParameter("username");

int count_user = 0;
String newPhoneNumber = null;
Statement statement0 = null;
ResultSet rs0 = null;
statement0 = connection.createStatement();
String query0 = "SELECT count(*) AS Count, PhoneNumber FROM Users WHERE AccountName = '" + newAccountName + "';";
rs0 = statement0.executeQuery(query0);
if(rs0.next()) {
count_user = Integer.parseInt(rs0.getString("Count"));
newPhoneNumber = rs0.getString("PhoneNumber");

}
statement0.close();

if(count_user == 0) {
out.print("user_not_logged_in");

}
else {
// seller cannot buy his/her own book
int count_sell = 0;
Statement statementE = null;
ResultSet rsE = null;
statementE = connection.createStatement();
String queryE = "SELECT COUNT(*) AS Count FROM Sell WHERE AccountName = '" + newAccountName + "' AND CopyID = " + newCopyID + " AND Status = 'Listed';";
rsE = statementE.executeQuery(queryE);
if(rsE.next()) {
count_sell = Integer.parseInt(rsE.getString("Count"));

}
statementE.close();

if(count_sell != 0) {
out.print("seller_cannot_buy");

}
else {
int count_record = 0;
Statement statement1 = null;
statement1 = connection.createStatement();
String query1 = "INSERT INTO Buy VALUES (DEFAULT, '" + newAccountName + "', " + newCopyID + ", DEFAULT, DEFAULT);";
count_record = statement1.executeUpdate(query1, Statement.NO_GENERATED_KEYS);
statement1.close();

if(count_record != 0) {
out.print("insert_successful");

// update availability and ownerEmail for book copy
Statement statement2 = null;
statement2 = connection.createStatement();
String query2 = "UPDATE BookCopies SET Availability = 'N/A' WHERE CopyID = " + newCopyID + ";";
statement2.executeUpdate(query2, Statement.NO_GENERATED_KEYS);
query2 = "UPDATE BookCopies SET OwnerEmail = '" + newAccountName + "' WHERE CopyID = " + newCopyID + ";";
statement2.executeUpdate(query2, Statement.NO_GENERATED_KEYS);
statement2.close();

// update status for sell
Statement statement3 = null;
statement3 = connection.createStatement();
String query3 = "UPDATE Sell SET Status = 'Sold' WHERE CopyID = " + newCopyID + ";";
statement3.executeUpdate(query3, Statement.NO_GENERATED_KEYS);
statement3.close();

String newISBN = null;
String newCopyCondition = null;
String newCopyPrice = null;
Statement statement4 = null;
ResultSet rs4 = null;
statement4 = connection.createStatement();
String query4 = "SELECT * FROM BookCopies WHERE CopyID = " + newCopyID + ";";
rs4 = statement4.executeQuery(query4);
if(rs4.next()) {
newISBN = rs4.getString("BookCopies.ISBN");
newCopyCondition = rs4.getString("BookCopies.CopyCondition");
newCopyPrice = rs4.getString("BookCopies.CopyPrice");

}
statement4.close();

String newBookTitle = null;
Statement statement5 = null;
ResultSet rs5 = null;
statement5 = connection.createStatement();
String query5 = "SELECT BookTitle FROM Books WHERE ISBN = '" + newISBN + "';";
rs5 = statement5.executeQuery(query5);
if(rs5.next()) {
newBookTitle = rs5.getString("Books.BookTitle");

}
statement5.close();

// inform seller
Statement statement6 = null;
ResultSet rs6 = null;
statement6 = connection.createStatement();
String query6 = "SELECT AccountName FROM Sell WHERE CopyID = " + newCopyID + ";";
rs6 = statement6.executeQuery(query6);
while(rs6.next()) {
String host="smtp.gmail.com", user="bookwarm.comp3111h@gmail.com", pass="bookwarmmobile";String SSL_FACTORY = "javax.net.ssl.SSLSocketFactory";
String to = rs6.getString("Sell.AccountName");
String from = "bookwarm.comp3111h@gmail.com"; 
String subject = "BookwarmMobile - Someone Wants to Buy Your Book";
String messageText = "<p>Someone who wants to buy your book listed on BookwarmMobile has shown up.</p><p>Please find attached the following details regarding the transaction.</p><p><blockquote>Book Title: <b>" + newBookTitle + "</b></blockquote></p><p><blockquote>ISBN: <b>" + newISBN + "</b></blockquote></p><p><blockquote>Condition: <b>" + newCopyCondition + "</b></blockquote></p><p><blockquote>Price: <b>" + newCopyPrice + "</b></blockquote></p><p>Please find attached his/her contacts details.</p><p><blockquote>Email: <b>" + newAccountName + "</b></blockquote></p><p><blockquote>Phone Number: <b>" + newPhoneNumber + "</b></blockquote></p><p>Please contact him/her directly at your earliest convenience to discuss the details for you to meet each other offline.</p><p>Thank you for using BookwarmMobile.</p>";
boolean sessionDebug = true;
Properties props = System.getProperties();
props.put("mail.host", host);
props.put("mail.transport.protocol.", "smtp");
props.put("mail.smtp.auth", "true");
props.put("mail.smtp.", "true");
props.put("mail.smtp.port", "465");
props.put("mail.smtp.socketFactory.fallback", "false");
props.put("mail.smtp.socketFactory.class", SSL_FACTORY);
Session mailSession = Session.getDefaultInstance(props, null);
mailSession.setDebug(sessionDebug);
Message msg = new MimeMessage(mailSession);
msg.setFrom(new InternetAddress(from));
InternetAddress[] address = {new InternetAddress(to)};
msg.setRecipients(Message.RecipientType.TO, address);
msg.setSubject(subject);
msg.setContent(messageText, "text/html"); 
Transport transport = mailSession.getTransport("smtp");
transport.connect(host, user, pass);

try {
transport.sendMessage(msg, msg.getAllRecipients());

}
catch (Exception err) {
out.println(err.getMessage());

}

transport.close();
}
statement6.close();

// inform buyer
String host="smtp.gmail.com", user="bookwarm.comp3111h@gmail.com", pass="bookwarmmobile";String SSL_FACTORY = "javax.net.ssl.SSLSocketFactory";
String to = newAccountName;
String from = "bookwarm.comp3111h@gmail.com"; 
String subject = "BookwarmMobile - Confirmation of Buy Transaction";
String messageText = "<p>Please find attached the following details regarding your recent buy transaction with BookwarmMobile.</p><p><blockquote>Book Title: <b>" + newBookTitle + "</b></blockquote></p><p><blockquote>ISBN: <b>" + newISBN + "</b></blockquote></p><p><blockquote>Condition: <b>" + newCopyCondition + "</b></blockquote></p><p><blockquote>Price: <b>" + newCopyPrice + "</b></blockquote></p><p>Your will be contacted by the seller of the book shortly.</p><p>Thank you for using BookwarmMobile.</p>";
boolean sessionDebug = true;
Properties props = System.getProperties();
props.put("mail.host", host);
props.put("mail.transport.protocol.", "smtp");
props.put("mail.smtp.auth", "true");
props.put("mail.smtp.", "true");
props.put("mail.smtp.port", "465");
props.put("mail.smtp.socketFactory.fallback", "false");
props.put("mail.smtp.socketFactory.class", SSL_FACTORY);
Session mailSession = Session.getDefaultInstance(props, null);
mailSession.setDebug(sessionDebug);
Message msg = new MimeMessage(mailSession);
msg.setFrom(new InternetAddress(from));
InternetAddress[] address = {new InternetAddress(to)};
msg.setRecipients(Message.RecipientType.TO, address);
msg.setSubject(subject);
msg.setContent(messageText, "text/html"); 
Transport transport = mailSession.getTransport("smtp");
transport.connect(host, user, pass);

try {
transport.sendMessage(msg, msg.getAllRecipients());

}
catch (Exception err) {
out.println(err.getMessage());

}

transport.close();

// update wishlist
Statement statement7 = null;
statement7 = connection.createStatement();
String query7 = "UPDATE WishList SET Status = 'Fulfilled' WHERE AccountName = '" + newAccountName + "' AND ISBN = '" + newISBN + "' AND Status = 'Listed';";
statement7.executeUpdate(query7, Statement.NO_GENERATED_KEYS);
statement7.close();

}
else {
out.print("insert_failed");

}

}

}

}

connection.close();

}
catch(Exception ex){
out.print(ex);

}
%>